package demo;

import com.example.demo.DemoApplication;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;

//@RunWith(SpringJUnit4ClassRunner.class)  这个用来测service
@SpringBootTest(classes = DemoApplication.class)
public class testApiExcel {

    //有点乱

    public static void main(String[] args) throws Exception {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet sheet = hssfWorkbook.createSheet();

        HSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("名称");
        row.createCell(1).setCellValue("类型");
        HSSFRow row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("1类型");
        row1.createCell(1).setCellValue("test");
        HSSFRow row2 = sheet.createRow(2);
        row2.createCell(0).setCellValue("2类型");
        row2.createCell(1).setCellValue("test2");


        String col = "JGLX";   //机构类型
        Map<String, String> boxMap = new HashMap<>();
        boxMap.put("JGLX", "1类型,2类型,3类型");
        //指定将下拉框添加至1-10行，0-0列。即第一列的第2到11行
        HSSFDataValidation dataValidation = createBox(col, boxMap, 1, 10, 0, 0);
        if (dataValidation != null) {
            sheet.addValidationData(dataValidation);
        }
        FileOutputStream out = new FileOutputStream("D:\\test1.xls");
        hssfWorkbook.write(out);
        out.close();
    }

    /**
     * excel导出，有码值的数据使用下拉框展示。
     *
     * @param col      列名
     * @param boxMap   码值集合
     * @param firstRow 插入下拉框开始行号
     * @param lastRow  插入下拉框结束行号
     * @param firstCol 插入下拉框开始列号
     * @param lastCol  插入下拉框结束行号
     * @return
     */
    public static HSSFDataValidation createBox(String col, Map<String, String> boxMap, int firstRow, int lastRow, int firstCol, int lastCol) {
        HSSFDataValidation dataValidation = null;
        //查询码值表
        String cols = "";
        if (null != boxMap.get(col)) {
            cols = boxMap.get(col);
        }
        //设置下拉框
        if (cols.length() > 0 && null != cols) {
            String str[] = cols.split(",");
            //指定0-9行，0-0列为下拉框
            CellRangeAddressList cas = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            //创建下拉数据列
            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str);
            //将下拉数据放入下拉框
            dataValidation = new HSSFDataValidation(cas, dvConstraint);
        }
        return dataValidation;
    }
}
